﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;

namespace Sinacor.Infra.Tools.ServiceRegistration.Data
{
    public class SubModule : ResourceAccess
    {
        #region Methods

        public List<Entity.SubModule> SelectSubModule(Entity.SubModule entity)
        {
            List<Entity.SubModule> response = null;

            StringBuilder sql = new StringBuilder();

            sql.AppendLine("SELECT ");
            sql.AppendLine("    COD_SUB_MOD, ");
            sql.AppendLine("    COD_MOD, ");
            sql.AppendLine("    NOME_SUB_MOD, ");
            sql.AppendLine("    NUM_PRTA_SERV, ");
            sql.AppendLine("    DATA_ALTE, ");
            sql.AppendLine("    (SELECT MAX(DATA_ALTE) FROM TFWDMOD) DATA_ALTE_MAX ");
            sql.AppendLine("FROM ");
            sql.AppendLine("    TFWDSUB_MOD ");
            sql.AppendLine("WHERE ");
            sql.AppendLine("    (COD_SUB_MOD = :COD_SUB_MOD OR :COD_SUB_MOD IS NULL) AND ");
            sql.AppendLine("    (COD_MOD = :COD_MOD OR :COD_MOD IS NULL) AND ");
            sql.AppendLine("    (UPPER(NOME_SUB_MOD) LIKE UPPER(:NOME_SUB_MOD) OR :NOME_SUB_MOD IS NULL) AND ");
            sql.AppendLine("    (NUM_PRTA_SERV = :NUM_PRTA_SERV OR :NUM_PRTA_SERV IS NULL) ");
            sql.AppendLine("ORDER BY NOME_SUB_MOD ");

            using (IDbCommand command = this.GetSqlStringCommand(sql.ToString()))
            {
                this.AddInParameter(command, "COD_SUB_MOD", DbType.Int32, entity.SubModuleId);
                this.AddInParameter(command, "COD_MOD", DbType.Int32, entity.Module != null ? entity.Module.ModuleId : null);
                this.AddInParameter(command, "NOME_SUB_MOD", DbType.String, AddCharacterLike(entity.SubModuleName));
                this.AddInParameter(command, "NUM_PRTA_SERV", DbType.Int32, entity.Port);

                response = this.FindAll<Entity.SubModule>(command,
                    (
                        reader => new Entity.SubModule
                        {
                            SubModuleId = reader.GetValue<int>("COD_SUB_MOD"),
                            SubModuleName = reader.GetValue<string>("NOME_SUB_MOD"),
                            Port = reader.GetValue<int>("NUM_PRTA_SERV"),
                            Services = GetServices(reader.GetValue<int>("COD_SUB_MOD")),
                            Module = entity.Module != null ? entity.Module : GetModule(reader.GetValue<int>("COD_MOD")),
                            DateChange = reader.GetValue<DateTime>("DATA_ALTE"),
                            IsItemDivergent = !reader.GetValue<DateTime>("DATA_ALTE").Equals(reader.GetValue<DateTime>("DATA_ALTE_MAX")),
                        }
                    )
                );
            }

            return response;
        }
        
        public Entity.SubModule InsertSubModule(Entity.SubModule entity)
        {
            StringBuilder sql = new StringBuilder();

            sql.AppendLine("INSERT INTO TFWDSUB_MOD ( ");
            sql.AppendLine("    COD_SUB_MOD, ");
            sql.AppendLine("    COD_MOD, ");
            sql.AppendLine("    NOME_SUB_MOD, ");
            sql.AppendLine("    NUM_PRTA_SERV, ");
            sql.AppendLine("    DATA_ALTE ");
            sql.AppendLine("    ) VALUES ( ");
            sql.AppendLine("    :COD_SUB_MOD, ");
            sql.AppendLine("    :COD_MOD, ");
            sql.AppendLine("    :NOME_SUB_MOD, ");
            sql.AppendLine("    :NUM_PRTA_SERV, ");

            if (entity.DataOrigin == Entity.DataOrigin.Loader)
                sql.AppendLine("    :DATA_ALTE) ");
            else
                sql.AppendLine("    (SELECT MAX(DATA_ALTE) FROM TFWDMOD)) ");

            using (IDbCommand command = this.GetSqlStringCommand(sql.ToString()))
            {
                this.AddInParameter(command, "COD_SUB_MOD", DbType.Int32, entity.SubModuleId);
                this.AddInParameter(command, "COD_MOD", DbType.Int32, entity.Module != null ? entity.Module.ModuleId : null);
                this.AddInParameter(command, "NOME_SUB_MOD", DbType.String, entity.SubModuleName);
                this.AddInParameter(command, "NUM_PRTA_SERV", DbType.Int32, entity.Port);

                if (entity.DataOrigin == Entity.DataOrigin.Loader)
                    this.AddInParameter(command, "DATA_ALTE", DbType.Date, entity.DateChange);

                this.ExecuteNonQuery(command);
            }

            return entity;
        }

        public void UpdateSubModule(Entity.SubModule entity)
        {
            StringBuilder sql = new StringBuilder();

            sql.AppendLine("UPDATE TFWDSUB_MOD SET ");
            sql.AppendLine("COD_SUB_MOD = :COD_SUB_MOD, ");
            sql.AppendLine("COD_MOD = :COD_MOD, ");
            sql.AppendLine("NOME_SUB_MOD = :NOME_SUB_MOD, ");
            sql.AppendLine("NUM_PRTA_SERV = :NUM_PRTA_SERV ");

            if (entity.DataOrigin == Entity.DataOrigin.Loader)
                sql.AppendLine(", DATA_ALTE = :DATA_ALTE ");

            sql.AppendLine("WHERE COD_SUB_MOD = :COD_SUB_MOD ");

            using (IDbCommand command = this.GetSqlStringCommand(sql.ToString()))
            {
                this.AddInParameter(command, "COD_SUB_MOD", DbType.Int32, entity.SubModuleId);
                this.AddInParameter(command, "COD_MOD", DbType.Int32, entity.Module != null ? entity.Module.ModuleId : null);
                this.AddInParameter(command, "NOME_SUB_MOD", DbType.String, entity.SubModuleName);
                this.AddInParameter(command, "NUM_PRTA_SERV", DbType.Int32, entity.Port);

                if (entity.DataOrigin == Entity.DataOrigin.Loader)
                    this.AddInParameter(command, "DATA_ALTE", DbType.Date, entity.DateChange);

                this.ExecuteNonQuery(command);
            }
        }

        public void DeleteSubModule(Entity.SubModule entity)
        {
            Service s = new Service();
            string sql = "DELETE FROM TFWDSUB_MOD WHERE COD_SUB_MOD = :COD_SUB_MOD ";

            s.DeleteServiceBySubModuleId(new Entity.Service { SubModule = new Entity.SubModule { SubModuleId = entity.SubModuleId } });

            using (IDbCommand command = this.GetSqlStringCommand(sql))
            {
                this.AddInParameter(command, "COD_SUB_MOD", DbType.Int32, entity.SubModuleId);
                this.ExecuteNonQuery(command);
            }
        }

        public void DeleteSubModuleByModuleId(Entity.SubModule entity)
        {
            Service s = new Service();
            string sql = "DELETE FROM TFWDSUB_MOD WHERE COD_MOD = :COD_MOD ";

            foreach (Entity.SubModule item in this.SelectSubModule(entity))
            {
                s.DeleteServiceBySubModuleId(new Entity.Service { SubModule = item });
            }

            using (IDbCommand command = this.GetSqlStringCommand(sql))
            {
                this.AddInParameter(command, "COD_MOD", DbType.Int32, entity.Module.ModuleId);
                this.ExecuteNonQuery(command);
            }
        }

        public int GetNextSubModuleId()
        {
            int retorno = 0;
            string sql = " SELECT NVL(MAX(COD_SUB_MOD) + 1, 1) FROM TFWDSUB_MOD ";

            using (IDbCommand command = this.GetSqlStringCommand(sql))
            {
                retorno = Convert.ToInt32(this.ExecuteScalar(command));
            }

            return retorno;
        }

        public int GetNextSubModulePort()
        {
            int retorno = 0;
            string sql = " SELECT NVL(MAX(NUM_PRTA_SERV) + 1, 1) FROM TFWDSUB_MOD ";

            using (IDbCommand command = this.GetSqlStringCommand(sql))
            {
                retorno = Convert.ToInt32(this.ExecuteScalar(command));
            }

            return retorno;
        }

        private List<Entity.Service> GetServices(int sub_module_id)
        {
            Service t = new Service();
            List<Entity.Service> response = null;

            if (sub_module_id != 0)
            {
                response = t.SelectService(new Entity.Service { SubModule = new Entity.SubModule { SubModuleId = sub_module_id } }, false);
            }

            return response;
        }

        private Entity.Module GetModule(int module_id)
        {
            Module s = new Module();
            List<Entity.Module> responseList = null;
            Entity.Module response = null;

            if (module_id != 0)
            {
                responseList = s.SelectModule(new Entity.Module { ModuleId = module_id });

                if (responseList != null && responseList.Count > 0)
                    response = responseList[0];
            }

            return response;
        }

        #endregion
    }
}
